ALTER DATABASE DaCredito COLLATE SQL_Latin1_General_CP1_CI_AI

/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2005                    */
/* Created on:     07/11/2011 21:53:11                          */
/*==============================================================*/


if exists (select 1
            from  sysobjects
           where  id = object_id('ACU_ACESSO_USUARIO')
            and   type = 'U')
   drop table ACU_ACESSO_USUARIO
go

if exists (select 1
            from  sysobjects
           where  id = object_id('AGC_AGENCIA')
            and   type = 'U')
   drop table AGC_AGENCIA
go

if exists (select 1
            from  sysobjects
           where  id = object_id('BNC_BANCO')
            and   type = 'U')
   drop table BNC_BANCO
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CID_CIDADE')
            and   type = 'U')
   drop table CID_CIDADE
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CLI_CLIENTE')
            and   type = 'U')
   drop table CLI_CLIENTE
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CNB_CONTA_BANCARIA')
            and   type = 'U')
   drop table CNB_CONTA_BANCARIA
go

if exists (select 1
            from  sysobjects
           where  id = object_id('EMP_EMPRESA')
            and   type = 'U')
   drop table EMP_EMPRESA
go

if exists (select 1
            from  sysobjects
           where  id = object_id('END_ENDERECO')
            and   type = 'U')
   drop table END_ENDERECO
go

if exists (select 1
            from  sysobjects
           where  id = object_id('EST_ESTADO')
            and   type = 'U')
   drop table EST_ESTADO
go

if exists (select 1
            from  sysobjects
           where  id = object_id('LCC_LANCAMENTO_CANCELADO')
            and   type = 'U')
   drop table LCC_LANCAMENTO_CANCELADO
go

if exists (select 1
            from  sysobjects
           where  id = object_id('LNC_LANCAMENTO_FINANCEIRO')
            and   type = 'U')
   drop table LNC_LANCAMENTO_FINANCEIRO
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TEL_TELEFONE')
            and   type = 'U')
   drop table TEL_TELEFONE
go

if exists (select 1
            from  sysobjects
           where  id = object_id('USR_USUARIO')
            and   type = 'U')
   drop table USR_USUARIO
go

/*==============================================================*/
/* Table: ACU_ACESSO_USUARIO                                    */
/*==============================================================*/
create table ACU_ACESSO_USUARIO (
   ACU_ID_ACESSO_USUARIO int                  not null,
   ACU_ID_USUARIO       int                  not null,
   ACU_DT_ACESSO        datetime             not null,
   constraint PK_ACU_ACESSO_USUARIO primary key (ACU_ID_ACESSO_USUARIO)
)
go

/*==============================================================*/
/* Table: AGC_AGENCIA                                           */
/*==============================================================*/
create table AGC_AGENCIA (
   AGC_ID_AGENCIA       int                  not null,
   AGC_ID_BANCO         int                  not null,
   AGC_NU_AGENCIA       varchar(6)           not null,
   AGC_NU_CNPJ          char(14)             null,
   AGC_NM_AGENCIA       varchar(50)          not null,
   constraint PK_AGC_AGENCIA primary key (AGC_ID_AGENCIA)
)
go

/*==============================================================*/
/* Table: BNC_BANCO                                             */
/*==============================================================*/
create table BNC_BANCO (
   BNC_ID_BANCO         int                  not null,
   BNC_NU_BANCO         char(3)              not null,
   BNC_NU_DIGITO_BANCO  char(1)              not null,
   BNC_NM_BANCO         varchar(50)          not null,
   BNC_NU_CNPJ          char(14)             not null,
   constraint PK_BNC_BANCO primary key (BNC_ID_BANCO)
)
go

/*==============================================================*/
/* Table: CID_CIDADE                                            */
/*==============================================================*/
create table CID_CIDADE (
   CID_ID_CIDADE        int                  not null,
   CID_ID_ESTADO        int                  not null,
   CID_NM_CIDADE        varchar(100)         not null,
   CID_CD_IBGE          numeric(7)           not null,
   constraint PK_CID_CIDADE primary key (CID_ID_CIDADE)
)
go

/*==============================================================*/
/* Table: CLI_CLIENTE                                           */
/*==============================================================*/
create table CLI_CLIENTE (
   CLI_ID_CLIENTE       int                  not null,
   CLI_NM_RAZAO_SOCIAL  varchar(100)         not null,
   CLI_NM_FANTASIA      varchar(100)         not null,
   CLI_NU_CNPJ          varchar(14)          not null,
   CLI_NU_INSCRICAO_ESTADUAL varchar(25)          null,
   CLI_NU_INSCRICAO_MUNICIPAL varchar(25)          null,
   CLI_CH_ATIVO         bit                  not null,
   CLI_DT_CADASTRO      datetime             null,
   constraint PK_CLI_CLIENTE primary key (CLI_ID_CLIENTE)
)
go

/*==============================================================*/
/* Table: CNB_CONTA_BANCARIA                                    */
/*==============================================================*/
create table CNB_CONTA_BANCARIA (
   CNB_ID_CONTA         int                  not null,
   CNB_TP_PROPRIETARIO  char(1)              not null,
   CNB_ID_CLIENTE       int                  null,
   CNB_ID_EMPRESA       int                  null,
   CNB_ID_AGENCIA       int                  not null,
   CNB_DS_CONTA         varchar(50)          not null,
   CNB_NU_CONTA         varchar(25)          not null,
   CNB_NU_DIGITO_CONTA  varchar(1)           not null,
   CNB_VL_SALDO_INICIAL decimal(2)           not null,
   CNB_CD_VARIACAO      char(2)              null,
   CNB_DT_ABERTURA      datetime             not null,
   constraint PK_CNB_CONTA_BANCARIA primary key (CNB_ID_CONTA)
)
go

/*==============================================================*/
/* Table: EMP_EMPRESA                                           */
/*==============================================================*/
create table EMP_EMPRESA (
   EMP_ID_EMPRESA       int                  not null,
   EMP_NM_EMPRESA       varchar(50)          not null,
   EMP_NU_CNPJ          char(14)             not null,
   constraint PK_EMP_EMPRESA primary key (EMP_ID_EMPRESA)
)
go

/*==============================================================*/
/* Table: END_ENDERECO                                          */
/*==============================================================*/
create table END_ENDERECO (
   END_ID_ENDERECO      int                  not null,
   END_ID_CIDADE        int                  not null,
   END_ID_CLIENTE       int                  not null,
   END_NM_LOGRADOURO    varchar(200)         not null,
   END_DS_COMPLEMENTO   varchar(100)         not null,
   END_NU_IMOVEL        varchar(6)           null,
   END_NM_BAIRRO        varchar(50)          null,
   END_NU_CEP           varchar(8)           not null,
   constraint PK_END_ENDERECO primary key (END_ID_ENDERECO)
)
go

/*==============================================================*/
/* Table: EST_ESTADO                                            */
/*==============================================================*/
create table EST_ESTADO (
   EST_ID_ESTADO        int                  not null,
   EST_SG_ESTADO        varchar(2)           not null,
   EST_NM_ESTADO        varchar(50)          not null,
   EST_CD_IBGE          numeric(7)            not null,

   constraint PK_EST_ESTADO primary key (EST_ID_ESTADO)
)
go

/*==============================================================*/
/* Table: LCC_LANCAMENTO_CANCELADO                              */
/*==============================================================*/
create table LCC_LANCAMENTO_CANCELADO (
   LCC_ID_LANCAMENTO    int                  not null,
   LCC_ID_CONTA         int                  not null,
   LCC_ID_USUARIO_LANCAMENTO int                  not null,
   LCC_ID_USUARIO_CANCELAMENTO int                  not null,
   LCC_DT_LANCAMENTO    datetime             not null,
   LCC_DT_REGISTRO      datetime             not null,
   LCC_VL_LANCAMENTO    decimal(2)           not null,
   LCC_TP_LANCAMENTO    varchar(1)           not null,
   LCC_TP_CREDITO_DEBITO varchar(1)           not null,
   LCC_DT_CANCELAMENTO  datetime             not null,
   constraint PK_LCC_LANCAMENTO_CANCELADO primary key (LCC_ID_LANCAMENTO)
)
go

/*==============================================================*/
/* Table: LNC_LANCAMENTO_FINANCEIRO                             */
/*==============================================================*/
create table LNC_LANCAMENTO_FINANCEIRO (
   LNC_ID_LANCAMENTO    int                  not null,
   LNC_ID_CONTA         int                  not null,
   LNC_DT_LANCAMENTO    datetime             not null,
   LNC_DT_REGISTRO      datetime             not null,
   LNC_VL_LANCAMENTO    decimal(2)           not null,
   LNC_TP_LANCAMENTO    varchar(1)           not null,
   LNC_TP_CREDITO_DEBITO varchar(1)           not null,
   constraint PK_LNC_LANCAMENTO_FINANCEIRO primary key (LNC_ID_LANCAMENTO)
)
go

/*==============================================================*/
/* Table: TEL_TELEFONE                                          */
/*==============================================================*/
create table TEL_TELEFONE (
   TEL_ID_TELEFONE      int                  not null,
   TEL_ID_CLIENTE       int                  not null,
   TEL_CD_DDD           varchar(4)           not null,
   TEL_NU_TELEFONE      varchar(11)          not null,
   constraint PK_TEL_TELEFONE primary key (TEL_ID_TELEFONE)
)
go

/*==============================================================*/
/* Table: USR_USUARIO                                           */
/*==============================================================*/
create table USR_USUARIO (
   USR_ID_USUARIO       int                  not null,
   USR_ID_CLIENTE       int                  null,
   USR_TP_USUARIO       char(1)              not null,
   USR_DS_NICKNAME      varchar(50)          not null,
   USR_DS_EMAIL         varchar(100)         not null,
   USR_DS_SENHA         varchar(25)          not null,
   USR_DS_NOME          varchar(50)          not null,
   USR_DS_SOBRENOME     varchar(150)         not null,
   constraint PK_USR_USUARIO primary key (USR_ID_USUARIO)
)
go


if exists (select 1
          from sysobjects
          where id = object_id('TD_TTF_TITULO_FINANCEIRO')
          and type = 'TR')
   drop trigger TD_TTF_TITULO_FINANCEIRO
go

if exists (select 1
          from sysobjects
          where id = object_id('TI_TTF_TITULO_FINANCEIRO')
          and type = 'TR')
   drop trigger TI_TTF_TITULO_FINANCEIRO
go

if exists (select 1
          from sysobjects
          where id = object_id('TU_TTF_TITULO_FINANCEIRO')
          and type = 'TR')
   drop trigger TU_TTF_TITULO_FINANCEIRO
go

/*==============================================================*/
/* Table: TTF_TITULO_FINANCEIRO                                 */
/*==============================================================*/
create table TTF_TITULO_FINANCEIRO (
   TTF_ID_TITULO        int                  not null,
   TTF_ID_CONVENIO      int                  null,
   TTF_ID_CLIENTE       int                  not null,
   TTF_ID_SACADO        int                  not null,
   TTF_VL_TITULO        money           not null,
   TTF_NU_NOSSO_NUMERO  char(20)             null,
   TTF_DT_EMISSAO_BOLETO datetime             null,
   TTF_DT_VENCIMENTO    datetime             not null,
   TTF_DT_CADASTRO      datetime             not null,
   TTF_NU_TITULO        int                  not null
)
go


create trigger TD_TTF_TITULO_FINANCEIRO on TTF_TITULO_FINANCEIRO for delete as
begin
    declare
       @numrows  int,
       @errno    int,
       @errmsg   varchar(255)

    select  @numrows = @@rowcount
    if @numrows = 0
       return

    /*  Cannot delete parent "TTF_TITULO_FINANCEIRO" if children still exist in "LNC_LANCAMENTO_FINANCEIRO"  */
    if exists (select 1
               from   LNC_LANCAMENTO_FINANCEIRO t2, deleted t1
               where  t2.LNC_ID_TITULO = t1.TTF_ID_TITULO)
       begin
          select @errno  = 50006,
                 @errmsg = 'Children still exist in "LNC_LANCAMENTO_FINANCEIRO". Cannot delete parent "TTF_TITULO_FINANCEIRO".'
          goto error
       end


    return

/*  Errors handling  */
error:
    raiserror @errno @errmsg
    rollback  transaction
end
go


create trigger TI_TTF_TITULO_FINANCEIRO on TTF_TITULO_FINANCEIRO for insert as
begin
    declare
       @numrows  int,
       @numnull  int,
       @errno    int,
       @errmsg   varchar(255)

    select  @numrows = @@rowcount
    if @numrows = 0
       return

    /*  Parent "CLI_CLIENTE" must exist when inserting a child in "TTF_TITULO_FINANCEIRO"  */
    if update(TTF_ID_CLIENTE)
    begin
       if (select count(*)
           from   CLI_CLIENTE t1, inserted t2
           where  t1.CLI_ID_CLIENTE = t2.TTF_ID_CLIENTE) != @numrows
          begin
             select @errno  = 50002,
                    @errmsg = 'Parent does not exist in "CLI_CLIENTE". Cannot create child in "TTF_TITULO_FINANCEIRO".'
             goto error
          end
    end
    /*  Parent "SAC_SACADO" must exist when inserting a child in "TTF_TITULO_FINANCEIRO"  */
    if update(TTF_ID_SACADO)
    begin
       if (select count(*)
           from   SAC_SACADO t1, inserted t2
           where  t1.SAC_ID_SACADO = t2.TTF_ID_SACADO) != @numrows
          begin
             select @errno  = 50002,
                    @errmsg = 'Parent does not exist in "SAC_SACADO". Cannot create child in "TTF_TITULO_FINANCEIRO".'
             goto error
          end
    end
    /*  Parent "CVN_CONVENIO" must exist when inserting a child in "TTF_TITULO_FINANCEIRO"  */
    if update(TTF_ID_CONVENIO)
    begin
       select @numnull = (select count(*)
                          from   inserted
                          where  TTF_ID_CONVENIO is null)
       if @numnull != @numrows
          if (select count(*)
              from   CVN_CONVENIO t1, inserted t2
              where  t1.CVN_ID_CONVENIO = t2.TTF_ID_CONVENIO) != @numrows - @numnull
          begin
             select @errno  = 50002,
                    @errmsg = 'Parent does not exist in "CVN_CONVENIO". Cannot create child in "TTF_TITULO_FINANCEIRO".'
             goto error
          end
    end

    return

/*  Errors handling  */
error:
    raiserror @errno @errmsg
    rollback  transaction
end
go


create trigger TU_TTF_TITULO_FINANCEIRO on TTF_TITULO_FINANCEIRO for update as
begin
   declare
      @numrows  int,
      @numnull  int,
      @errno    int,
      @errmsg   varchar(255)

      select  @numrows = @@rowcount
      if @numrows = 0
         return

      /*  Parent "CLI_CLIENTE" must exist when updating a child in "TTF_TITULO_FINANCEIRO"  */
      if update(TTF_ID_CLIENTE)
      begin
         if (select count(*)
             from   CLI_CLIENTE t1, inserted t2
             where  t1.CLI_ID_CLIENTE = t2.TTF_ID_CLIENTE) != @numrows
            begin
               select @errno  = 50003,
                      @errmsg = 'CLI_CLIENTE" does not exist. Cannot modify child in "TTF_TITULO_FINANCEIRO".'
               goto error
            end
      end
      /*  Parent "SAC_SACADO" must exist when updating a child in "TTF_TITULO_FINANCEIRO"  */
      if update(TTF_ID_SACADO)
      begin
         if (select count(*)
             from   SAC_SACADO t1, inserted t2
             where  t1.SAC_ID_SACADO = t2.TTF_ID_SACADO) != @numrows
            begin
               select @errno  = 50003,
                      @errmsg = 'SAC_SACADO" does not exist. Cannot modify child in "TTF_TITULO_FINANCEIRO".'
               goto error
            end
      end
      /*  Parent "CVN_CONVENIO" must exist when updating a child in "TTF_TITULO_FINANCEIRO"  */
      if update(TTF_ID_CONVENIO)
      begin
         select @numnull = (select count(*)
                            from   inserted
                            where  TTF_ID_CONVENIO is null)
         if @numnull != @numrows
            if (select count(*)
                from   CVN_CONVENIO t1, inserted t2
                where  t1.CVN_ID_CONVENIO = t2.TTF_ID_CONVENIO) != @numrows - @numnull
            begin
               select @errno  = 50003,
                      @errmsg = 'CVN_CONVENIO" does not exist. Cannot modify child in "TTF_TITULO_FINANCEIRO".'
               goto error
            end
      end
      /*  Cannot modify parent code in "TTF_TITULO_FINANCEIRO" if children still exist in "LNC_LANCAMENTO_FINANCEIRO"  */
      if update(TTF_ID_TITULO)
      begin
         if exists (select 1
                    from   LNC_LANCAMENTO_FINANCEIRO t2, inserted i1, deleted d1
                    where  t2.LNC_ID_TITULO = d1.TTF_ID_TITULO
                     and  (i1.TTF_ID_TITULO != d1.TTF_ID_TITULO))
            begin
               select @errno  = 50005,
                      @errmsg = 'Children still exist in "LNC_LANCAMENTO_FINANCEIRO". Cannot modify parent code in "TTF_TITULO_FINANCEIRO".'
               goto error
            end
      end


      return

/*  Errors handling  */
error:
    raiserror @errno @errmsg
    rollback  transaction
end
go
